const { queryMysql } = require("../utils/queryMysqlUtil");
const { isDef } = require("../utils/utils");
const dayjs = require("dayjs");
const { execSync } = require("child_process");
const { getQuarter, getQuarterObj } = require("../utils/DateUtil");

exports.softDeleteCodeCountDetail = async function (rid) {
  const now = dayjs().format("YYYY-MM-DD HH:mm:ss");
  let { results } = await queryMysql(
    `select * from chihiro_code_count where id=${rid}`
  ).catch((err) => {
    return Promise.reject(err);
  });

  if (Array.isArray(results) && results.length === 1) {
    // console.log(results[0]['delete_time'])
    if (results[0]["delete_time"] === null) {
      await queryMysql(
        `update chihiro_code_count  set delete_time='${now}' where id=${rid}`
      ).catch((err) => {
        return Promise.reject(err);
      });
    } else {
      await queryMysql(
        `update chihiro_code_count  set delete_time=null where id=${rid}`
      ).catch((err) => {
        return Promise.reject(err);
      });
    }
  }

  return true;
};

exports.getCodeCountListByDate = async function (dateObj) {
  const { startDate, endDate, pageSize, pageNum, hasZero, totalNumAsc } =
    dateObj;

  let queryStr = null;
  let queryTotalCount = null;
  if (isDef(startDate) && isDef(endDate)) {
    queryStr = `
        select chihiro_code_count.id as rid,gitlab_name,gitlab_clone_url,add_num,remove_num,total_num,chihiro_code_count.create_time as create_time,chihiro_code_count.delete_time as delete_time
        from chihiro_code_count inner 
        join chihiro_gitlab_detail 
        on chihiro_code_count.repo_rid=chihiro_gitlab_detail.id
        where chihiro_code_count.create_time between '${startDate} 00:00:00' and '${endDate} 23:59:59'
        ${!hasZero ? "and total_num<>0" : ""}
        order by chihiro_code_count.create_time desc ${
          totalNumAsc ? " ,total_num asc" : ""
        }
        LIMIT ${pageSize}
        OFFSET ${(pageNum - 1) * pageSize}`;

    queryTotalCount = `SELECT COUNT(*)  
        FROM chihiro_code_count INNER JOIN chihiro_gitlab_detail ON chihiro_code_count.repo_rid=chihiro_gitlab_detail.id 
        WHERE chihiro_code_count.create_time between '${startDate} 00:00:00' AND '${endDate} 23:59:59' ${
      !hasZero ? "and total_num<>0" : ""
    }`;
  } else if (!isDef(startDate) && !isDef(endDate)) {
    queryStr = `SELECT chihiro_code_count.id as rid,gitlab_name,gitlab_clone_url,add_num,remove_num,total_num,chihiro_code_count.create_time AS create_time,chihiro_code_count.delete_time AS delete_time  
        FROM chihiro_code_count INNER JOIN chihiro_gitlab_detail ON chihiro_code_count.repo_rid=chihiro_gitlab_detail.id 
        ${!hasZero ? "where total_num<>0" : ""}
        order by chihiro_code_count.create_time desc ${
          totalNumAsc ? ",total_num asc" : ""
        }
        LIMIT ${pageSize} 
        OFFSET ${(pageNum - 1) * pageSize}`;

    queryTotalCount = `SELECT COUNT(*)  
        FROM chihiro_code_count INNER JOIN chihiro_gitlab_detail ON chihiro_code_count.repo_rid=chihiro_gitlab_detail.id ${
          !hasZero ? "where  total_num<>0" : ""
        }`;
  } else {
    return {
      totalCount: 0,
      results: [],
    };
  }

  // console.log(queryStr)
  // console.log(queryTotalCount)

  let { results } = await queryMysql(queryStr).catch((err) => {
    return Promise.reject(err);
  });

  let totalCountRes = await queryMysql(queryTotalCount).catch((err) => {
    return Promise.reject(err);
  });

  return {
    totalCount: totalCountRes.results[0]["COUNT(*)"],
    results,
  };
};

module.exports.getCodeCountListSummary = async function (branchId) {
  const queryStr = `
      select CONCAT(YEAR(chihiro_code_count.create_time),'Q',QUARTER(chihiro_code_count.create_time)) AS quarter,  sum(add_num) as allCount,sum(remove_num) as allRemove,sum(total_num) as allTotal
      from chihiro_team_group
      left join chihiro_gitlab_detail
      on chihiro_team_group.id=chihiro_gitlab_detail.group_id
      left join chihiro_code_count
      on chihiro_gitlab_detail.id=chihiro_code_count.repo_rid
      where chihiro_team_group.branch_id=${branchId} and chihiro_code_count.delete_time is null
      group by quarter
      having  quarter >= '2021Q4';
    `;

  const res = await queryMysql(queryStr);

  return res.results;
};


module.exports.getCodeCountByBranchId = async function (
  branchId,
  startNyrDate,
  endNyrDate,
  pageNum,
  pageSize
) {
  const queryStr = `
   select temp_chihiro_code_count.id as rid,chihiro_team_group.id as group_id, chihiro_team_group.group_name,chihiro_gitlab_detail.gitlab_name,add_num,remove_num,total_num,temp_chihiro_code_count.create_time,temp_chihiro_code_count.delete_time
    from chihiro_branch
    left join chihiro_team_group
    on chihiro_branch.id=chihiro_team_group.branch_id
    left join chihiro_gitlab_detail
    on chihiro_team_group.id=chihiro_gitlab_detail.group_id
    join (
        select * 
        from chihiro_code_count
        where chihiro_code_count.create_time between '${startNyrDate} 00:00:00' and '${endNyrDate} 23:59:59'  and (add_num<>0 or add_num<>0 or total_num<>0)
    ) temp_chihiro_code_count
    on chihiro_gitlab_detail.id=temp_chihiro_code_count.repo_rid
    where chihiro_branch.id=${branchId}
    limit ${pageSize}
    offset ${(pageNum - 1) * pageSize};
  `;

  const countStr = `
   select count(*)
    from chihiro_branch
    left join chihiro_team_group
    on chihiro_branch.id=chihiro_team_group.branch_id
    left join chihiro_gitlab_detail
    on chihiro_team_group.id=chihiro_gitlab_detail.group_id
    join (
        select * 
        from chihiro_code_count
        where chihiro_code_count.create_time between '${startNyrDate} 00:00:00' and '${endNyrDate} 23:59:59'  and (add_num<>0 or add_num<>0 or total_num<>0)
    ) temp_chihiro_code_count
    on chihiro_gitlab_detail.id=temp_chihiro_code_count.repo_rid
    where chihiro_branch.id=${branchId};
  `;
  const { results } = await queryMysql(queryStr);
  const countRes = await queryMysql(countStr);

  return {
    countList: results,
    total: countRes.results[0]["count(*)"] || 0,
  };
};

// 返回BranchId部门下各组，在季度quarter期间的代码量汇总
module.exports.getGroupSummaryByBranchId = async function (branchId, quarter) {
  // const queryStr = `
  //   select chihiro_team_group.id,chihiro_team_group.group_name,sum(add_num) as add_num,sum(remove_num) as remove_num,sum(total_num) total_num
  //   from chihiro_team_group
  //   left join chihiro_gitlab_detail
  //   on chihiro_team_group.id=chihiro_gitlab_detail.group_id
  //   left join (
  //       select *
  //       from chihiro_code_count
  //       where  delete_time is null and CONCAT(YEAR(chihiro_code_count.create_time), 'Q', QUARTER(chihiro_code_count.create_time)) = '${quarter}'
  //   ) temp_chihiro_code_count
  //   on chihiro_gitlab_detail.id=temp_chihiro_code_count.repo_rid
  //   where chihiro_team_group.branch_id=${branchId}
  //   group by chihiro_team_group.id,chihiro_team_group.group_name
  // `;
  //quarter 2022Q1
  const [year, quarterNum] = quarter.split("Q");
  const [startNyrDate, endNyrDate] = getQuarterObj(year, `Q${quarterNum}`).span;
  const queryStr = `
    select chihiro_team_group.id,chihiro_team_group.group_name,sum(add_num) as add_num,sum(remove_num) as remove_num,sum(total_num) total_num
    from chihiro_team_group
    left join chihiro_gitlab_detail
    on chihiro_team_group.id=chihiro_gitlab_detail.group_id 
    left join (
        select *
        from chihiro_code_count
        where  chihiro_code_count.create_time between '${startNyrDate} 00:00:00' and '${endNyrDate} 23:59:59'  
    ) temp_chihiro_code_count 
    on chihiro_gitlab_detail.id=temp_chihiro_code_count.repo_rid
    where chihiro_team_group.branch_id=${branchId}
    group by chihiro_team_group.id,chihiro_team_group.group_name
  `;

  const res = await queryMysql(queryStr);
  return res.results;
};

// 返回BranchId部门在 startNyrDate - endNyrDate 期间的代码量汇总 (即一条 新增、删除、总和 数据)
module.exports.getBranchSummaryByBranchId = async function (
    branchId,
    startNyrDate,
    endNyrDate
){
  const  queryStr=`
    select sum(add_num) as add_num,sum(remove_num) as remove_num,sum(total_num) as total_num
    from chihiro_team_group 
    left join chihiro_gitlab_detail
    on chihiro_team_group.id=chihiro_gitlab_detail.group_id
    left join chihiro_code_count
    on chihiro_gitlab_detail.id=chihiro_code_count.repo_rid
    where chihiro_team_group.branch_id=${branchId} and chihiro_code_count.create_time between'${startNyrDate} 00:00:00' and '${endNyrDate} 23:59:59'
  `
  const res = await queryMysql(queryStr);
  return res.results[0];
}